import Router from 'koa-router';
import { pool } from '../../config/database';
import { authMiddleware } from '../../middleware/auth';
import { JwtPayload, ApiResponse } from '../../types';

const router = new Router({ prefix: '/api/system/sql' });

// 执行购物车表创建SQL（临时接口，仅管理员可用）
router.post('/create-cart-table', authMiddleware, async (ctx) => {
  try {
    const user = ctx.state.user as JwtPayload;
    
    // 检查管理员权限
    if (user.email !== 'admin@example.com') {
      ctx.status = 403;
      ctx.body = {
        success: false,
        message: '只有管理员可以执行此操作'
      } as ApiResponse;
      return;
    }

    // 创建购物车表的SQL
    const createTableSQL = `
      CREATE TABLE IF NOT EXISTS cart_items (
        id int(11) NOT NULL AUTO_INCREMENT COMMENT '购物车项目ID',
        user_id int(11) NOT NULL COMMENT '用户ID',
        product_id int(11) NOT NULL COMMENT '产品ID',
        quantity int(11) NOT NULL DEFAULT 1 COMMENT '商品数量',
        specs text DEFAULT NULL COMMENT '商品规格(JSON格式)',
        selected tinyint(1) NOT NULL DEFAULT 1 COMMENT '是否选中(0:未选中, 1:选中)',
        created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
        updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
        PRIMARY KEY (id),
        KEY idx_user_id (user_id) COMMENT '用户ID索引',
        KEY idx_product_id (product_id) COMMENT '产品ID索引',
        KEY idx_user_product (user_id, product_id) COMMENT '用户-产品联合索引',
        KEY idx_created_at (created_at) COMMENT '创建时间索引',
        KEY idx_selected (selected) COMMENT '选中状态索引'
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='购物车表'
    `;

    // 执行创建表的SQL
    await pool.query(createTableSQL);

    // 检查表是否创建成功
    const [tableCheck] = await pool.query(
      "SHOW TABLES LIKE 'cart_items'"
    );

    if (Array.isArray(tableCheck) && tableCheck.length > 0) {
      // 获取表结构
      const [tableStructure] = await pool.query(
        "DESCRIBE cart_items"
      );

      ctx.body = {
        success: true,
        message: '购物车表创建成功！',
        data: {
          tableName: 'cart_items',
          structure: tableStructure
        }
      } as ApiResponse;
    } else {
      ctx.body = {
        success: false,
        message: '表创建失败，请检查数据库连接'
      } as ApiResponse;
    }

  } catch (error: any) {
    console.error('创建购物车表失败:', error);
    ctx.status = 500;
    ctx.body = {
      success: false,
      message: '创建购物车表失败：' + error.message
    } as ApiResponse;
  }
});

// 检查购物车表状态
router.get('/check-cart-table', authMiddleware, async (ctx) => {
  try {
    const user = ctx.state.user as JwtPayload;
    
    // 检查管理员权限
    if (user.email !== 'admin@example.com') {
      ctx.status = 403;
      ctx.body = {
        success: false,
        message: '只有管理员可以执行此操作'
      } as ApiResponse;
      return;
    }

    // 检查表是否存在
    const [tableCheck] = await pool.query(
      "SHOW TABLES LIKE 'cart_items'"
    );

    if (Array.isArray(tableCheck) && tableCheck.length > 0) {
      // 获取表结构
      const [tableStructure] = await pool.query(
        "DESCRIBE cart_items"
      );

      // 获取表中数据数量
      const [countResult] = await pool.query(
        "SELECT COUNT(*) as count FROM cart_items"
      ) as [any[], any];

      ctx.body = {
        success: true,
        message: '购物车表已存在',
        data: {
          exists: true,
          tableName: 'cart_items',
          structure: tableStructure,
          recordCount: countResult[0].count
        }
      } as ApiResponse;
    } else {
      ctx.body = {
        success: true,
        message: '购物车表不存在',
        data: {
          exists: false
        }
      } as ApiResponse;
    }

  } catch (error: any) {
    console.error('检查购物车表失败:', error);
    ctx.status = 500;
    ctx.body = {
      success: false,
      message: '检查购物车表失败：' + error.message
    } as ApiResponse;
  }
});

export default router; 